/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.informet.ipd.modelo.dao;

import com.informet.ipd.modelo.vo.APermanenteVO;
import com.informet.ipd.modelo.vo.FisiMetaVO;
import com.informet.ipd.modelo.vo.MActividadVO;
import com.informet.ipd.modelo.vo.ResponsableVO;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author LAPTOP01
 */
public class APermanenteDao extends DBase {

    Connection conn = null;

    public APermanenteDao()  {
        super();
    }

    private void setAPermanente(APermanenteVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODACTPER_PK(rs.getInt("NUM_CODACTPERM_PK"));
        bean.setTX_DESCACTPER(rs.getString("TX_DESCACTPERM"));
        bean.setTX_DESCRESP(rs.getString("TX_DESCDEPEN"));
        bean.setNUM_CODDEPEN_PK(rs.getInt("NUM_CODDEPEN_PK"));
        bean.setNUM_CODOBJESP_PK(rs.getInt("NUM_CODOBJESP_PK"));
        bean.setNUM_IDOBJ(rs.getInt("NUM_IDOBJ"));
        bean.setNUM_IDOBJESP(rs.getInt("NUM_IDOBJESP"));
        bean.setNUM_IDACTPER(rs.getInt("NUM_IDACTPERM"));
        
         
        
    }
    
    private void setAPermanente2(APermanenteVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODACTPER_PK(rs.getInt("NUM_CODACTPER_PK"));
       //bean.setTX_RESPONACTPER(rs.getString("TX_RESPONACTPER"));
        //bean.setTX_DESCACTPER(rs.getString("TX_DESCACTPER"));

    }

    private void setmaximo(APermanenteVO bean, ResultSet rs) throws SQLException {
        bean.setMaximo(rs.getInt("maximo"));
       //bean.setTX_RESPONACTPER(rs.getString("TX_RESPONACTPER"));
        //bean.setTX_DESCACTPER(rs.getString("TX_DESCACTPER"));
    }

     private void setAPermanenteCombo(APermanenteVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODACTPER_PK(rs.getInt("NUM_CODACTPER_PK"));
        bean.setNUM_CODOBJESP_PK(rs.getInt("NUM_CODOBJESP_PK"));
        bean.setTX_DESCACTPER(rs.getString("TX_DESCACTPER"));
  }

     private void setAPermanenteComboMod(APermanenteVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODACTPER_PK(rs.getInt("NUM_CODACTPER_PK"));
        bean.setNUM_CODOBJESP_PK(rs.getInt("NUM_CODOBJESP_PK"));
        bean.setTX_DESCACTPER(rs.getString("TX_DESCACTPER"));
  }
      private void setResponsable(ResponsableVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODRESP_PK(rs.getInt("NUM_CODDEPEN_PK"));
        bean.setTX_DESCRESP(rs.getString("TX_DESCDEPEN"));
        
  }




	private void setAPermanenteUnion(APermanenteVO bean,MActividadVO bean2, ResultSet rs) throws SQLException {
        bean.setNUM_CODACTPER_PK(rs.getInt("NUM_CODACTPER_PK"));
        bean.setNUM_CODOBJESP_PK(rs.getInt("NUM_CODOBJESP_PK"));
        bean.setNUM_PRESUACTPER(rs.getInt("NUM_PRESUACTPER"));
        bean.setTX_DESCACTPER("TX_DESCACTPER");
    }

    public String spMan(int tipo, APermanenteVO bean) {
        String msj = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_ACTPER(?,?,?,?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setInt(2, bean.getNUM_CODACTPER_PK());
            cs.setString(3, bean.getTX_DESCACTPER());
            cs.setInt(4, bean.getNUM_CODOBJESP_PK());
            cs.setDouble(5, bean.getNUM_PRESUACTPER());
            cs.setInt(6, bean.getNUM_CODDEPEN_PK());
            cs.setInt(7, bean.getNUM_IDACTPER());
            cs.execute();



        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "procedure"+e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }


    public String spManM(int tipo, APermanenteVO bean) {
        String msj = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_ACTPERM(?,?,?,?,?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setInt(2, bean.getNUM_CODACTPER_PK());
            cs.setString(3, bean.getTX_DESCACTPER());
            cs.setInt(4, bean.getNUM_CODOBJESP_PK());
            cs.setDouble(5, bean.getNUM_PRESUACTPER());
            cs.setInt(6, bean.getNUM_CODRESP_PK());
            cs.setInt(7, bean.getMaximo());
            cs.setInt(8, bean.getNUM_CODDEPEN_PK());
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "procedure"+e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }

    public ArrayList<APermanenteVO> selectLista() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<APermanenteVO> lista = new ArrayList<APermanenteVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODACTPER_PK,TX_DESCACTPER from ME_ACTPER";
            rs = stmt.executeQuery(sql);
            APermanenteVO bean = null;
            while (rs.next()) {
                bean = new APermanenteVO();
                setAPermanente(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

    public List getlist() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<APermanenteVO> lista = new ArrayList<APermanenteVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODACTPER_PK,TX_DESCACTPER,NUM_CODRESP_PK from ME_ACTPER";
            rs = stmt.executeQuery(sql);
            APermanenteVO bean = null;
            while (rs.next()) {
                bean = new APermanenteVO();
                setAPermanente(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }



    public int maximo() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<APermanenteVO> lista = new ArrayList<APermanenteVO>(0);
        String sql = "";
        int max=0;
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select max(NUM_CODACTPER_PK) as maximo from ME_ACTPER";
            rs = stmt.executeQuery(sql);
            APermanenteVO bean = null;
            while (rs.next()) {
                bean = new APermanenteVO();
                setmaximo(bean, rs);
                max=bean.getMaximo();
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return max;
    }



    
    public List getlist2() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<APermanenteVO> lista = new ArrayList<APermanenteVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODACTPER_PK,TX_DESCACTPER,NUM_CODRESP_PK from ME_ACTPER";
            rs = stmt.executeQuery(sql);
            APermanenteVO bean = null;
            while (rs.next()) {
                bean = new APermanenteVO();
                setAPermanente2(bean, rs);
                lista.add(bean);
                //JOptionPane.showMessageDialog(null, bean.getNUM_CODACTPER_PK());
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }
    
    
    
    
    
    
    
    
 public List getListById(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<APermanenteVO> lista = new ArrayList<APermanenteVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            sql = "select og.NUM_IDOBJ,oe.NUM_IDOBJESP,actpm.NUM_IDACTPERM,actpm.NUM_CODACTPERM_PK,actpm.NUM_CODDEPEN_PK,actpm.TX_DESCACTPERM,dep.TX_DESCDEPEN,actpm.NUM_CODOBJESP_PK  from ME_OBJESP oe inner join ME_OBJ og on og.NUM_CODOBJ_PK=oe.NUM_CODOBJ_PK inner join ME_ACTPERM actpm on  actpm.NUM_CODOBJESP_PK=oe.NUM_CODOBJESP_PK  inner join ME_DEPENDENCIA dep on actpm.NUM_CODDEPEN_PK=dep.NUM_CODDEPEN_PK where actpm.NUM_CODOBJESP_PK=? order by NUM_IDACTPERM";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            APermanenteVO bean = null;
            while (rs.next()) {
                bean = new APermanenteVO();
                setAPermanente(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

    public APermanenteVO selectById(int id) throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Connection conn = null;
        APermanenteVO bean = null;
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            String sql = "select NUM_CODACTPER_PK,TX_DESCACTPER from ME_ACTPER where NUM_CODACTPER_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            bean = new APermanenteVO();
            if (rs.next()) {
                setAPermanente(bean, rs);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }
        }

        return bean;
    }


     public List getCombo(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<APermanenteVO> lista = new ArrayList<APermanenteVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);

            sql = "select NUM_CODACTPER_PK,TX_DESCACTPER,NUM_CODOBJESP_PK from ME_ACTPER where NUM_CODOBJESP_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            APermanenteVO bean = null;
            while (rs.next()) {
                bean = new APermanenteVO();
                setAPermanenteCombo(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }



     public List getComboMod(int id,int id2) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<APermanenteVO> lista = new ArrayList<APermanenteVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);

            sql = "select NUM_CODACTPER_PK,TX_DESCACTPER,NUM_CODOBJESP_PK from ME_ACTPER inner join ME_DEPENDENCIA on ME_DEPENDENCIA.NUM_CODDEPEN_PK=ME_ACTPER.NUM_CODDEPEN_PK where NUM_CODOBJESP_PK=? AND ME_DEPENDENCIA.NUM_CODDEPEN_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            pstmt.setInt(2, id2);
            rs = pstmt.executeQuery();
            APermanenteVO bean = null;
            while (rs.next()) {
                bean = new APermanenteVO();
                setAPermanenteComboMod(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }
     
     public List getFiltro(int id) throws SQLException {
         PreparedStatement pstmt = null;
         Statement stmt = null;
         ResultSet rs = null;
         ArrayList<APermanenteVO> lista = new ArrayList<APermanenteVO>(0);
         //lista=null;
         String sql = "";
         try {
             conn = DriverManager.getConnection(url, usuario, password);
             sql = "select NUM_CODACTPER_PK from ME_ACTPER where NUM_CODOBJESP_PK=?";
             pstmt = conn.prepareStatement(sql);
             pstmt.setInt(1, id);
             rs = pstmt.executeQuery();
             APermanenteVO bean = null;
             while (rs.next()) {
                 bean = new APermanenteVO();
                 setAPermanente2(bean, rs);
                 lista.add(bean);
             }
             rs.close();
             pstmt.close();
         } finally {
             if (rs != null) {
                 rs.close();
                 rs = null;
             }
             if (stmt != null) {
                 stmt.close();
                 stmt = null;
             }
             if (conn != null) {
                 conn.close();
                 conn = null;
             }

         }
         return lista;
     }

    public List getResponsables()throws SQLException {
               PreparedStatement pstmt = null;
         Statement stmt = null;
         ResultSet rs = null;
         ArrayList<ResponsableVO> lista = new ArrayList<ResponsableVO>(0);
         //lista=null;
         String sql = "";
         try {
             conn = DriverManager.getConnection(url, usuario, password);
             sql = "select NUM_CODDEPEN_PK,TX_DESCDEPEN from dbo.ME_DEPENDENCIA";
             pstmt = conn.prepareStatement(sql);
            
             rs = pstmt.executeQuery();
             ResponsableVO bean = null;
             while (rs.next()) {
                 bean = new ResponsableVO();
                 setResponsable(bean, rs);
                 lista.add(bean);
             }
             rs.close();
             pstmt.close();
         } finally {
             if (rs != null) {
                 rs.close();
                 rs = null;
             }
             if (stmt != null) {
                 stmt.close();
                 stmt = null;
             }
             if (conn != null) {
                 conn.close();
                 conn = null;
             }

         }
         return lista;
    }
}
